package com.nbsaas.boot.sql;

import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;

import java.util.ArrayList;
import java.util.List;

public class SqlWhereAppender {

    public static String append(String sql, List<SqlCondition> conditions) throws Exception {
        Statement stmt = CCJSqlParserUtil.parse(sql);
        if (!(stmt instanceof Select)) throw new IllegalArgumentException("Only SELECT supported");

        PlainSelect body = (PlainSelect) ((Select) stmt).getSelectBody();

        Expression combinedCondition = body.getWhere();

        for (SqlCondition cond : conditions) {
            Expression expr = buildExpression(cond);
            if (expr != null) {
                combinedCondition = (combinedCondition == null)
                        ? expr
                        : new AndExpression(combinedCondition, expr);
            }
        }

        body.setWhere(combinedCondition);
        return stmt.toString();
    }

    private static Expression buildExpression(SqlCondition cond) {
        Column column = new Column(cond.getField());
        String op = cond.getOperator().toUpperCase();

        switch (op) {
            case "=":
                return new EqualsTo(column, toValue(cond.getValue()));
            case ">=":
                return new GreaterThanEquals(column, toValue(cond.getValue()));
            case "<=":
                return new MinorThanEquals(column, toValue(cond.getValue()));
            case "!=":
            case "<>":
                return new NotEqualsTo(column, toValue(cond.getValue()));
            case ">":
                return new GreaterThan(column, toValue(cond.getValue()));
            case "<":
                return new MinorThan(column, toValue(cond.getValue()));
            case "LIKE":
                LikeExpression likeExpression = new LikeExpression();
                likeExpression.setLeftExpression(column);
                likeExpression.setRightExpression(toValue(cond.getValue()));
                return likeExpression;
            case "IN":
                ParenthesedExpressionList list = new ParenthesedExpressionList();
                for (Object v : (List<?>) cond.getValue()) {
                    list.add(toValue(v));
                }
                return new InExpression(column, list);
            case "BETWEEN":
                return new Between()
                        .withLeftExpression(column)
                        .withBetweenExpressionStart(toValue(cond.getValue()))
                        .withBetweenExpressionEnd(toValue(cond.getValue2()));
            default:
                throw new UnsupportedOperationException("Unsupported operator: " + op);
        }
    }

    private static Expression toValue(Object val) {
        if (val instanceof Number) {
            return new LongValue(val.toString());
        } else if (val instanceof Boolean) {
            return new BooleanValue(val.toString());
        } else {
            return new StringValue(val.toString());
        }
    }
}
